import mysql from 'mysql2/promise';

// 数据库配置
const dbConfig = {
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '123456',
  database: process.env.DB_NAME || 'asset_management',
  port: parseInt(process.env.DB_PORT || '3390'),
  charset: 'utf8mb4'
};

// 创建数据库连接池
export const pool = mysql.createPool({
  ...dbConfig,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

// 初始化数据库和表
export async function initDatabase() {
  try {
    // 创建数据库（如果不存在）
    const tempConnection = await mysql.createConnection({
      host: dbConfig.host,
      user: dbConfig.user,
      password: dbConfig.password,
      port: dbConfig.port
    });

    await tempConnection.execute(`CREATE DATABASE IF NOT EXISTS \`${dbConfig.database}\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci`);
    await tempConnection.end();

    // 创建表
    await createTables();
    
    console.log('✅ MySQL数据库初始化成功');
  } catch (error) {
    console.error('❌ 数据库初始化失败:', error);
    throw error;
  }
}

// 创建数据库表
async function createTables() {
  const connection = await pool.getConnection();
  
  try {
    // 用户表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS users (
        id VARCHAR(36) PRIMARY KEY,
        username VARCHAR(50) UNIQUE NOT NULL,
        password VARCHAR(255) NOT NULL,
        role ENUM('admin', 'user') DEFAULT 'user',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      )
    `);

    // 资产表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS assets (
        id VARCHAR(36) PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        category VARCHAR(50) NOT NULL,
        model VARCHAR(100),
        serial_number VARCHAR(100),
        purchase_date DATE,
        price DECIMAL(10,2),
        location VARCHAR(100),
        status ENUM('active', 'maintenance', 'retired', '正常', '维修中', '报废') DEFAULT 'active',
        assigned_to VARCHAR(100),
        description TEXT,
        created_by VARCHAR(36),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (created_by) REFERENCES users(id)
      )
    `);

    // 盘点记录表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS inventory_records (
        id VARCHAR(36) PRIMARY KEY,
        asset_id VARCHAR(36) NOT NULL,
        type ENUM('盘入', '盘出') NOT NULL,
        quantity INT DEFAULT 1,
        reason VARCHAR(200),
        operator_id VARCHAR(36) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (asset_id) REFERENCES assets(id) ON DELETE CASCADE,
        FOREIGN KEY (operator_id) REFERENCES users(id)
      )
    `);

    // 执行数据库迁移
    await runMigrations(connection);
    
    // 创建默认管理员账户
    await createDefaultAdmin(connection);
    
    console.log('✅ 数据库表创建成功');
  } finally {
    connection.release();
  }
}

// 执行数据库迁移
async function runMigrations(connection: mysql.PoolConnection) {
  try {
    // 检查 assigned_to 列是否存在，如果不存在则添加
    const [columns] = await connection.execute(`
      SELECT COLUMN_NAME 
      FROM INFORMATION_SCHEMA.COLUMNS 
      WHERE TABLE_SCHEMA = DATABASE() 
      AND TABLE_NAME = 'assets' 
      AND COLUMN_NAME = 'assigned_to'
    `);
    
    if ((columns as any[]).length === 0) {
      await connection.execute(`
        ALTER TABLE assets 
        ADD COLUMN assigned_to VARCHAR(100) AFTER status
      `);
      console.log('✅ 已添加 assigned_to 列到 assets 表');
    }
  } catch (error) {
    console.error('数据库迁移失败:', error);
  }
}

// 创建默认管理员账户
async function createDefaultAdmin(connection: mysql.PoolConnection) {
  const CryptoJS = require('crypto-js');
  const { v4: uuidv4 } = require('uuid');
  
  try {
    const [rows] = await connection.execute('SELECT COUNT(*) as count FROM users WHERE role = "admin"');
    const adminCount = (rows as any)[0].count;
    
    if (adminCount === 0) {
      const adminId = uuidv4();
      const hashedPassword = CryptoJS.MD5('admin123').toString();
      
      await connection.execute(
        'INSERT INTO users (id, username, password, role) VALUES (?, ?, ?, ?)',
        [adminId, 'admin', hashedPassword, 'admin']
      );
      
      console.log('✅ 默认管理员账户创建成功 (用户名: admin, 密码: admin123)');
    }
  } catch (error) {
    console.error('创建默认管理员失败:', error);
  }
}

// 执行查询的辅助函数
export async function query(sql: string, params: any[] = []) {
  const connection = await pool.getConnection();
  try {
    const [results] = await connection.execute(sql, params);
    return results;
  } finally {
    connection.release();
  }
}

// 关闭数据库连接池
export async function closeDatabase() {
  await pool.end();
}
